﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data.SqlClient;
using System.Collections;
using smartGardenWebService;
using System.Data;
using System.Threading;

namespace WebServiceSample
{
    /// <summary>
    /// SampleService 的摘要说明
    /// </summary>
    [WebService(Namespace = "http://SmartGarden.com/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // 若要允许使用 ASP.NET AJAX 从脚本中调用此 Web 服务，请取消注释以下行。 
    // [System.Web.Script.Services.ScriptService]
    public class SampleService : System.Web.Services.WebService
    {
        private string sqlConnStr = System.Configuration.ConfigurationManager.ConnectionStrings["smartGardenConnectinString"].ConnectionString;
        //获取对应区域的数据：温度、空气湿度、光照强度、雨滴数据、土壤湿度、数据接受时间
        //参数：模式号,区域号
        [WebMethod]
        public int[] GetData(string modeId, int area)
        {
            string sql = "SELECT TOP (1) * FROM dealReceive  WHERE area = '" + area + "' and modeId = " + modeId + " ORDER BY ID DESC";
            SqlConnection sqlconn = new SqlConnection();
            sqlconn.ConnectionString = sqlConnStr;
            sqlconn.Open();
            SqlCommand sqlcomm = new SqlCommand(sql, sqlconn);
            SqlDataReader sth = sqlcomm.ExecuteReader();        //执行数据库查询语句并存储返回数据            
            int[] newData = new int[6];
            while (sth.Read())                                  //读取数据信息                
            {
                newData[0] = (int)sth["temperature"];           //采集对应区域最后一次温度数据
                newData[1] = (int)sth["humidity"];              //空气湿度
                newData[2] = (int)sth["illumination"];          //光照强度
                newData[3] = (int)sth["rain"];                  //天气
                newData[4] = (int)sth["soilHum"];               //土壤湿度
                newData[5] = (int)sth["receiveTime"];
            }
            sqlconn.Close();
            return newData;
        }

        //更新报表数据
        [WebMethod]
        public void GetBaoBiao(string aera,string modeId)
        {
           // string sql = "EXEC dbo.sec_dealReceive" + aera;
           // string connstring = @"Data Source=localhost;Initial Catalog=smartGarden;uid=sa;pwd=123";
                SqlConnection sqlconn = new SqlConnection();
                sqlconn.ConnectionString = sqlConnStr;
                sqlconn.Open();
                SqlCommand sqlcomm = new SqlCommand();
                sqlcomm.CommandType = CommandType.StoredProcedure;
                sqlcomm.CommandText = "sec_dealReceive";
                sqlcomm.Connection = sqlconn;
                SqlParameter pCustomerID = new SqlParameter();
                //pCustomerID.ParameterName = "@area";
                //pCustomerID.SqlDbType = SqlDbType.NChar;
                //pCustomerID.Value = aera;
                //sqlcomm.Parameters.Add(pCustomerID);
                pCustomerID = sqlcomm.Parameters.Add("@area", SqlDbType.NVarChar); 
                pCustomerID.Direction=ParameterDirection.Input;
                pCustomerID.Value=aera;
                pCustomerID = sqlcomm.Parameters.Add("@modeId", SqlDbType.NVarChar);
                pCustomerID.Direction = ParameterDirection.Input;
                pCustomerID.Value = modeId;
                sqlcomm.ExecuteNonQuery();
                sqlconn.Close();
            //string connstring = @"Data Source=localhost;Initial Catalog=Northwind;uid=sa;pwd=";
        }

        //修改数据库语句
        //修改
        [WebMethod]
        public int ManFacAction(string orderFrom, string state, string modeId, string area, string facNo)
        {
            string sqlComm = "update modeFacility set state = '" + state + "' where modeId = '" + modeId + "' and area = '" + area + "' and facility = '" + facNo + "'";
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = sqlConnStr;
            conn.Open();
            SqlCommand command = new SqlCommand(sqlComm, conn);
            FacAction(orderFrom, state, modeId, area, facNo);
            return command.ExecuteNonQuery();
        }

        public void FacAction(string orderFrom, string state, string modeId, string area, string facNo)
        {
            string orderSend = facNo + state;
            string instruction = "";
            switch (orderSend)
            {
                case "M10": instruction = "关闭风机"; break;
                case "M11": instruction = "开启风机"; break;
                case "M20": instruction = "关闭加热灯"; break;
                case "M21": instruction = "开启加热灯"; break;
                case "M30": instruction = "关闭水泵"; break;
                case "M31": instruction = "开启水泵"; break;
                case "M40": instruction = "关闭天窗"; break;
                case "M41": instruction = "开启天窗"; break;
                case "M50": instruction = "关闭白炽灯"; break;
                case "M51": instruction = "开启白炽灯"; break;
            }
            instruction = orderFrom + "端" + instruction;
            string sqlComm = "insert into actionInstruction (modeId,orderFrom,area,facilitiesNo,mode,orderSend,instruction,actionTime)  values('" + modeId + "','" + orderFrom + "','" + area + "','1','MAN','" + orderSend + "','" + instruction + "',getdate())";
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = sqlConnStr;
            conn.Open();
            SqlCommand command = new SqlCommand(sqlComm, conn);
            command.ExecuteNonQuery();
        }


        #region 开启手动模式
        /// <summary>
        /// 开启手动模式
        /// </summary>
        /// <param name="orderFrom">网页端/手机端/桌面端</param>
        /// <param name="modeId">模式id</param>
        /// <param name="area">区域号</param>
        /// <returns></returns>
        [WebMethod]
        public int switchManu(string orderFrom, int modeId, int area)
        {
            string manMode = "insert into actionInstruction(modeId,orderFrom,area,facilitiesNo,mode,orderSend,instruction,actionTime) values('" + modeId + "','" + orderFrom + "','" + area + "','1','MAN','" + orderFrom + "MAN1','" + orderFrom + "端开启手动模式',getdate())";
            string closeAuto = "insert into actionInstruction(modeId,orderFrom,area,facilitiesNo,mode,orderSend,instruction,actionTime) values('" + modeId + "','" + orderFrom + "','" + area + "','1','AUTO','" + orderFrom + "AUTO0','" + orderFrom + "端关闭自动模式',getdate())";
            if (insertDb(manMode) && insertDb(closeAuto))
            {
                return 1;
            }
            return 0;
        }
        #endregion


        //自动模式设定范围值
        //并插入自动模式说明
        //orderFrom：指令来自端  
        //dataArra 为  modeId-area-tempMin-tempMax-illMin-illMiax-soilHumMin-soilHumMax
        [WebMethod]
        public int getAutoValue(string orderFrom, string dataArr)
        {
            var dataArray = dataArr.Split('-');
            var data = new int[8];
            for (var i = 0; i < 8; i++)
            {
                if (dataArray.Length > i)
                {
                    data[i] = int.TryParse(dataArray[i] ?? string.Empty, out data[i])
                        ? data[i]
                        : 0;
                }
                else
                {
                    data[i] = 0;
                }
            }
            string sql = "insert into autoModeValue(modeId,area,tempMin,tempMax,illMin,illMax,soilHumMin,soilHumMax,setTime)values('" + data[0] + "','" + data[1] + "','" + data[2] + "','" + data[3] + "','" + data[4] + "','" + data[5] + "','" + data[6] + "','" + data[7] + "',getdate())";
            string actionInstuction = "insert into actionInstruction(modeId,orderFrom,area,facilitiesNo,mode,orderSend,instruction,actionTime) values('" + data[0] + "','" + orderFrom + "','" + data[1] + "','1','AUTO','" + orderFrom + "AUTO1','" + orderFrom + "端开启自动模式',getdate())";

            if (insertDb(sql) && insertDb(actionInstuction))
            {
                return 1;
            }
            return 0;
        }

        #region 插入数据库语句
        /// <summary>
        /// 插入数据库语句
        /// </summary>
        /// <param name="sqlComm">sql语句</param>
        /// <returns>bool</returns>
        public bool insertDb(string sqlComm)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = sqlConnStr;

            conn.Open();

            SqlCommand sqlCommand = new SqlCommand(sqlComm, conn);

            sqlCommand.ExecuteNonQuery();
            conn.Close();
            return true;
        } 
        #endregion

        /// <summary>
        /// 获取对应模式名称下模式id
        /// </summary>
        /// <param name="modeName">模式名称</param>
        /// <returns>modeId</returns>
        [WebMethod]
        public string[] GetModeArea(string modeName)
        {
            string modeId = GetModeId(modeName);
            string sql = "select area from modeArea where modeId = '" + modeId + "'";
            SqlConnection sqlconn = new SqlConnection();
            sqlconn.ConnectionString = sqlConnStr;
            sqlconn.Open();
            SqlCommand sqlcomm = new SqlCommand(sql, sqlconn);
            SqlDataReader sth = sqlcomm.ExecuteReader();
            ArrayList temp = new ArrayList();
            while (sth.Read())
            {
                temp.Add(sth["area"]);
            }
            sqlconn.Close();
            string[] modeArea = new string[temp.Count];
            for (int i = 0; i < temp.Count; i++)
            {
                modeArea[i] = temp[i].ToString();
            }
            return modeArea;
        }

        /// <summary>
        /// 登录方法
        /// </summary>
        /// <param name="account">账号</param>
        /// <param name="psw">密码</param>
        /// <returns>string 1/0</returns>
        [WebMethod]
        public string getLogin(string account, string psw)
        {
            string flag = "0";
            string sql = "select count(*) as countName from userAccount where account = '" + account + "' and password = '" + psw + "'";
            SqlConnection sqlconn = new SqlConnection();
            sqlconn.ConnectionString = sqlConnStr;
            sqlconn.Open();
            SqlCommand sqlcomm = new SqlCommand(sql, sqlconn);
            SqlDataReader sth = sqlcomm.ExecuteReader();
            while (sth.Read())
            {
                flag = sth["countName"].ToString();
            }
            sqlconn.Close();
            return flag;
        }

        //获取模式编号
        //参数：模式名称
        //modeId 设为全局变量
        [WebMethod]
        public string GetModeId(string modeName)
        {
            string sql = "SELECT modeId from mode where modeName = '" + modeName + "'";
            SqlConnection sqlconn = new SqlConnection();
            sqlconn.ConnectionString = sqlConnStr;
            sqlconn.Open();
            SqlCommand sqlcomm = new SqlCommand(sql, sqlconn);
            SqlDataReader sth = sqlcomm.ExecuteReader();        //执行数据库查询语句并存储返回数据            
            string modeId = "";
            while (sth.Read())                                  //读取数据信息                
            {
                modeId = sth["modeId"].ToString();           //采集对应区域最后一次温度数据               
            }
            sqlconn.Close();
            return modeId;
        }

        //获取模式名称：智能花园、高尔夫、智能农业、自定义等
        [WebMethod]
        public string[] GetMode()
        {
            string sql = "select modeName from mode order by modeId ";
            SqlConnection sqlconn = new SqlConnection();
            sqlconn.ConnectionString = sqlConnStr;
            sqlconn.Open();
            SqlCommand sqlcomm = new SqlCommand(sql, sqlconn);
            SqlDataReader sth = sqlcomm.ExecuteReader();
            ArrayList temp = new ArrayList();
            while (sth.Read())
            {
                if (temp.ToString().Trim() != "自定义".ToString())
                    temp.Add(sth["modeName"]);
            }
            sqlconn.Close();
            string[] mode = (string[])temp.ToArray(typeof(string));
            return mode;
        }

        //获取对应模式下所有的区域号
        [WebMethod]
        public int[] GetModeAllArea(string modeName)
        {
            string modeId = GetModeId(modeName);
            string sql = "select area from modeArea where modeId = '" + modeId + "' ";
            SqlConnection sqlconn = new SqlConnection();
            sqlconn.ConnectionString = sqlConnStr;
            sqlconn.Open();
            SqlCommand sqlcomm = new SqlCommand(sql, sqlconn);
            SqlDataReader sth = sqlcomm.ExecuteReader();
            ArrayList temp = new ArrayList();
            while (sth.Read())
            {
                temp.Add(sth["area"]);
            }
            sqlconn.Close();
            int[] modeArea = (int[])temp.ToArray(typeof(int));
            return modeArea;
        }

        //根据模式判断模式下应有的 设备 M1、M2、M3...
        //返回  1、2、3、、、、
        [WebMethod]
        public int[] chkModeFac(string modeId)
        {
            /*查找对应模式下对应区域存有设备*/
            string sql = "select distinct facilityNo from facility f ,modeFacility m where m.facility = f.facilityNo and m.modeId = '" + modeId + "'";
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = sqlConnStr;
            conn.Open();
            SqlCommand comm = new SqlCommand(sql, conn);
            SqlDataReader sth = comm.ExecuteReader();
            ArrayList temp = new ArrayList();
            while (sth.Read())
            {
                temp.Add(sth["facilityNo"]);
            }
            int[] modeFac = new int[temp.Count];
            for (int i = 0; i < temp.Count; i++)
            {
                modeFac[i] = int.Parse(temp[i].ToString().Substring(1, 1));
            }
            return modeFac;
        }

        //根据模式判断模式下应有的
        //监控数据  如：温度、湿度等
        [WebMethod]
        public int[] chkModeSensor(string modeId)
        {
            /*查找对应模式下对应区域存有设备*/
            string sql = "select sensor from modeSensor where modeId = '" + modeId + "'";
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = sqlConnStr;
            conn.Open();
            SqlCommand comm = new SqlCommand(sql, conn);
            SqlDataReader sth = comm.ExecuteReader();
            ArrayList temp = new ArrayList();
            while (sth.Read())
            {
                temp.Add(sth["sensor"]);
            }
            int[] modeSensor = new int[temp.Count];
            for (int i = 0; i < temp.Count; i++)
            {
                modeSensor[i] = int.Parse(temp[i].ToString().Substring(1, 1));
            }
            return modeSensor;
        }
    }
}
